USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_CREA_MORA_60_DP]    Script Date: 11/23/2011 14:39:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_D0007_CREA_MORA_60_DP]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_D0007_CREA_MORA_60_DP]
GO

USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_CREA_MORA_60_DP]    Script Date: 11/23/2011 14:39:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WASP_D0007_CREA_MORA_60_DP]
AS

-- QUERY CREACION TABLAS SISTEMA GESTION MORA
--select count(*) from #GMDR a, #2GMDR b where a.cuenta = b.cuenta and a.operacion = b.operacion and a.total = b.total
-- MORA MENOR A 60 DIAS
-- QUERY CREACION TABLAS SISTEMA GESTION MORA
--select count(*) from #GMDR a, #2GMDR b where a.cuenta = b.cuenta and a.operacion = b.operacion and a.total = b.total
-- MORA MENOR A 60 DIAS
truncate table GESTION_MORA.dbo.GMMM60
insert into GESTION_MORA.dbo.GMMM60
select
	 cuenta = SCCTA
	,operacion = SCOPER
	,Total = CONVERT(NUMERIC, SUM(SCSDO))
from
	gestion2.dbo.fsd011(nolock)
WHERE     
	(SCRUB BETWEEN 1130001000 AND 1130004000) OR  -------
	(SCRUB BETWEEN 1330001000 AND 1330005000) OR  
	(SCRUB BETWEEN 1163001000 AND 1163005000) OR
	(SCRUB BETWEEN 1363001000 AND 1363005000) OR
	(SCRUB BETWEEN 1173002000 AND 1173004500) OR
	(SCRUB BETWEEN 1373002000 AND 1373004500) OR
         ------SECURITIZADO-------------------------
        (SCRUB BETWEEN 8830000000 AND 8830004000) OR 
        (SCRUB BETWEEN 8863000000 AND 8863004000) OR 
        (SCRUB BETWEEN 8930000000 AND 8930004000) OR
        (SCRUB BETWEEN 8963000000 AND 8963004000)
GROUP BY SCCTA, SCOPER
HAVING      SUM(SCSDO) <> 0

--DEUDOR PROVICIONAL
truncate table  GESTION_MORA.dbo.GMDP
insert into GESTION_MORA.dbo.GMDP
select 
	cuenta = Sccta
	,Operacion = SCOPER
	,Total = CONVERT(NUMERIC, SUM(SCSDO)) 
FROM         gestion2.dbo.FSD011(NOLOCK)
WHERE     (SCRUB BETWEEN 1140001000 AND 1140009000) OR
          (SCRUB BETWEEN 1340001000 AND 1340005000) OR
          (SCRUB BETWEEN 1340011000 AND 1340015000) OR
          (SCRUB BETWEEN 1164001000 AND 1164009000) OR
          (SCRUB BETWEEN 1364001000 AND 1364009000) OR
          (SCRUB BETWEEN 1175002000 AND 1175008500) OR
          (SCRUB BETWEEN 1373102000 AND 1373114500) OR
           ------SECURITIZADO-------------------------
          (SCRUB BETWEEN 8840000000 AND 8840008000) OR 
          (SCRUB BETWEEN 8864000000 AND 8864008000) OR 
          (SCRUB BETWEEN 8940000000 AND 8940014000) OR
          (SCRUB BETWEEN 8964000000 AND 8964008000)
GROUP BY SCCTA, SCOPER
HAVING      SUM(SCSDO) <> 0

-- DUDOSA RECUPERACION
truncate table GESTION_MORA.dbo.GMDR
insert into GESTION_MORA.dbo.GMDR
select 
	cuenta = Sccta
	,Operacion = SCOPER
	,Total = CONVERT(NUMERIC, SUM(SCSDO)) 
FROM         
	gestion2.dbo.FSD011(NOLOCK)
WHERE     
	(SCRUB BETWEEN 1150001000 AND 1150009000) OR
	(SCRUB BETWEEN 1350001000 AND 1350005000) OR
	(SCRUB BETWEEN 1350011000 AND 1350015000) OR
	(SCRUB BETWEEN 1165001000 AND 1165009000) OR
	(SCRUB BETWEEN 1365001000 AND 1365008000) OR
	(SCRUB BETWEEN 1177002000 AND 1177008500) OR
	(SCRUB BETWEEN 1373202000 AND 1373214500) OR
        ------SECURITIZADO-------------------------
        (SCRUB BETWEEN 8850000000 AND 8850008000) OR 
        (SCRUB BETWEEN 8865000000 AND 8865008000) OR 
        (SCRUB BETWEEN 8950000000 AND 8950014000) OR
        (SCRUB BETWEEN 8965000000 AND 8965008000)
GROUP BY SCCTA, SCOPER
HAVING      SUM(SCSDO) <> 0

-- Cuentas 
TRUNCATE TABLE GESTION_MORA.DBO.GMCuentas
INSERT INTO  GESTION_MORA.DBO.GMCuentas
SELECT TOP 100 PERCENT Cuenta, Operacion
FROM         dbo.GMMM60(nolock)
UNION
SELECT TOP 100 PERCENT Cuenta, Operacion
FROM         dbo.GMDP(nolock)
UNION
SELECT TOP 100 PERCENT Cuenta, Operacion
FROM         dbo.GMDR(nolock)
GROUP BY Cuenta, Operacion
ORDER BY Cuenta, Operacion

-- Empresas
TRUNCATE TABLE GESTION_MORA.DBO.GMEmpresas
INSERT INTO GESTION_MORA.DBO.GMEmpresas
SELECT     
	Cuenta_Empresa = a.ctnro
	,RutDV =  b.Pendoc 
	,Estado_Empresa = c.Z07304DSC
	,Condicion_Empresa = g.Cclnom
	,Nombre_Empresa =  d.Pjrazs 
	,Clasificacion_Empresa = f.Z07315DSC 
	,SucOper = ba.Z07325OPE
	,Actividad_Economica = a.Ctnroi
FROM         
	gestion2.dbo.Z07324 AS e (nolock) 
	,gestion2.dbo.z07315 AS f (nolock) 
	,gestion2.dbo.FST049 AS g(nolock) 
	,gestion2.dbo.FSD008 AS a(nolock) 
	,gestion2.dbo.Z07325 AS ba (nolock) 
	,gestion2.dbo.FSR008 AS b (nolock) 
	,gestion2.dbo.Z07304 AS c (nolock) 
	,gestion2.dbo.FSD003 AS d(nolock) 
WHERE
	e.Z07315COD = f.Z07315COD AND
	g.Ctccli = a.Ctccli AND
	a.Ctnro = ba.Z07325CTA AND
	 a.Ctnro = b.Ctnro AND
	 ba.Z07304COD = c.Z07304COD AND
	 b.Pendoc = d.Pjndoc AND
	e.Z07324NDO = b.Pendoc AND
	(a.Ctccli IN (1, 2, 6)) AND (e.Z07324NDO <> '')

-- Fallecidos
TRUNCATE TABLE  GESTION_MORA.DBO.GMFallecidos

INSERT INTO 	 GESTION_MORA.DBO.GMFallecidos
SELECT     
	Cuenta = CONVERT(INT, SUBSTRING(Pfndoc, 1, LEN(Pfndoc) - 1))
	,Fech_Fall = Pfffal  
FROM         
	gestion2.dbo.FSD002(nolock)
WHERE     
	Pfndoc <> '' AND 
	Pffal = 'S'
GROUP BY Pfndoc, Pfffal

-- TME
TRUNCATE TABLE GESTION_MORA.DBO.GMTME
INSERT INTO GESTION_MORA.DBO.GMTME
SELECT     
	Z073B1Emp
	,Z073B1Cta
	,Z073B3Mod
	,Z073B3Suc
	,Z073B3Mda
	,Z073B3Pap
	,Z073B3Cta
	,Z073B3Oper
	,Z073B3Sbop
	,Z073B3Tope
	,Z073B3Fpag = CONVERT(varchar, Z073B3Fpag, 103)
	,Z073B3Tipo
	,Z073B3Rub
	,Z073B3Stat
	,Z073B3FSt
	,Z073B3EExt
	,Z073B3Cno = CONVERT(NUMERIC, Z073B3Cno)
	,Z073B3CRe
	,Z073B3Cas
	,NombreDeudor = ctnom 
FROM         
	gestion2.dbo.z073b3(nolock)
	,gestion2.dbo.fsd008(nolock)
WHERE     
	z073b1cta > 50000000 AND 
	z073b3stat = 'S' AND 
	z073b3cta = ctnro

-- TMP
TRUNCATE TABLE GESTION_MORA.DBO.GMTMP
INSERT INTO GESTION_MORA.DBO.GMTMP
SELECT     
	Z073B1Emp
	,Z073B1Cta
	,Z073B3Mod
	,Z073B3Suc
	,Z073B3Mda
	,Z073B3Pap
	,Z073B3Cta
	,Z073B3Oper
	,Z073B3Sbop
	,Z073B3Tope
	,Z073B3Fpag = CONVERT(varchar, Z073B3Fpag, 103)
	,Z073B3Tipo
	,Z073B3Rub
	,Z073B3Stat
	,Z073B3FSt
	,Z073B3EExt
	,Z073B3Cno = CONVERT(NUMERIC, Z073B3Cno)
	,Z073B3CRe
	,Z073B3Cas
	,NombreDeudor = ctnom  
FROM         
	gestion2.dbo.z073b3(nolock)
	,gestion2.dbo.fsd008(nolock)
WHERE     
--	z073b1cta < 50000000 AND 
	z073b3stat = 'S' AND 
	z073b3cta = ctnro
	
-- MORA
TRUNCATE TABLE GESTION_MORA.DBO.GMMora
INSERT INTO GESTION_MORA.DBO.GMMora
SELECT   
	z073b3.Z073B1Emp
	,z073b3.Z073B1Cta
	,z073b3.Z073B3Mod
	,z073b3.Z073B3Suc
	,z073b3.Z073B3Mda
	,z073b3.Z073B3Pap
	,z073b3.Z073B3Cta
	,z073b3.Z073B3Oper
	,z073b3.Z073B3Sbop
	,z073b3.Z073B3Tope
	,z073b3.Z073B3Fpag
	,z073b3.Z073B3Tipo
	,z073b3.Z073B3Rub
	,z073b3.Z073B3Stat
	,z073b3.Z073B3FSt
	,z073b3.Z073B3EExt
	,z073b3.Z073B3CNo
	,z073b3.Z073B3CRe
	,z073b3.Z073B3Cas
	,FSD008.CTNOM
FROM         
	gestion2.dbo.z073b3 as z073b3
       ,gestion2.dbo.fsd008 as fsd008
WHERE     
	z073b3stat = 'S' AND 
	z073b3cta = ctnro

-- REVISAR CUANDO ESTEN LAS TABLAS DISPONIBLES
-- ME
TRUNCATE TABLE GESTION_MORA.DBO.GMME
INSERT INTO GESTION_MORA.DBO.GMME
SELECT 
	DISTINCT TOP 100 PERCENT 
	Z073B3Cta
	,Z073B3Oper
FROM         
	gestion2.dbo.z073b3(nolock)
	,gestion2.dbo.fsd008(nolock)
WHERE     
	z073b1cta > 50000000 AND 
	z073b3stat = 'S' AND 
	z073b3cta = ctnro
GROUP BY Z073B3Cta, Z073B3Oper
ORDER BY Z073B3Cta, Z073B3Oper

-- REVISAR CUANDO ESTEN LAS TABLAS DISPONIBLES
-- MP
TRUNCATE TABLE GESTION_MORA.DBO.GMMP
INSERT INTO GESTION_MORA.DBO.GMMP
SELECT 
	DISTINCT TOP 100 PERCENT 
	Z073B3Cta
	,Z073B3Oper
FROM         
	gestion2.dbo.z073b3(nolock)
	,gestion2.dbo.fsd008(nolock)
WHERE     
	z073b1cta < 50000000 AND 
	z073b3stat = 'S' AND 
	z073b3cta = ctnro
GROUP BY Z073B3Cta, Z073B3Oper
ORDER BY Z073B3Cta, Z073B3Oper

-- Sucursales
TRUNCATE TABLE GESTION_MORA.DBO.GMSucursales
INSERT INTO GESTION_MORA.DBO.GMSucursales
SELECT     
	SucOper = Sucurs 
	,Agencia = Scnom 
FROM         
	gestion2.dbo.fst001(nolock)
WHERE     
	(Sucurs <= 79)
GO

